package a_aa_amainpackage.lvyou_clear.lv_update.util

import com.alibaba.fastjson.JSON
import com.mongodb.spark.MongoSpark
import com.mongodb.spark.config.ReadConfig
import org.apache.spark.SparkContext
import org.apache.spark.rdd.RDD
import org.apache.spark.sql.{DataFrame, SQLContext, SparkSession}
import org.elasticsearch.spark._

/*
*项目名: o2odata_commend
*文件名: updaue_address
*创建者: hancongcong
*创建时间:2019/12/25 18:04
*描述: TODO
*/ object updaue_addressfirst {

  val platform = "tuniu"


  /**
    * 旅游和邮轮
    *
    * @param sc
    * @param sqlContext
    * @param year
    * @param month
    * @param plat_type
    */
  def lyandyl(es_data: DataFrame, sc: SparkContext, sqlContext: SQLContext, year: String, month: String, plat_type: String): DataFrame = {
    //            println("lyandyl：：：：month:" + s"${month}")
    //            val es_data: RDD[String] = sc.esJsonRDD(s"${year}_update_${platform}_${plat_type}/${platform}_${plat_type}_${year}_${month}").values
    val frame = es_data
    frame
      .drop("region_id_info", "region_id", "economic_division", "economic_division_out", "economic_division_info")
      .registerTempTable("es_data")
    //出发地省市正确，需要关联区域id 读取标准地址表
    val address = sqlContext.read.option("header", "true")
      .csv("s3a://o2o-dimension-table/address_table/address_table_2019/address_mapping_standard/2019_9/")
      .where("district='0'")
    address.createOrReplaceTempView("address")
    val result_ly_data = sqlContext.sql(
      """
        |select
        |t1.*,
        |case when t2.city is null then '0' else t2.uniqueid end as region_id_info,
        |case when t2.city is null then '0' else t2.economic_division end as economic_division_info
        |from
        |es_data t1
        |left join
        |address t2
        |on t1.province_info=t2.province and t1.city_info=t2.city
              """.stripMargin).dropDuplicates("product_Id")
    //修改目的地省市
    val mudidi_data = result_ly_data.toJSON.rdd.map(line => {
      val nObject = JSON.parseObject(line)

      nObject.toJSONString
    })
    //修改目的地region_id
    sqlContext.read.json(mudidi_data)
      .registerTempTable("mudidi_data")
    val result_data = sqlContext.sql(
      """
        |select
        |t1.*,
        |case when t2.city is null then '0' else t2.uniqueid end as region_id,
        |case when t2.city is null then '0' else t2.economic_division end as economic_division_out
        |from
        |mudidi_data t1
        |left join
        |address t2
        |on t1.province_out=t2.province and t1.city_out=t2.city
              """.stripMargin).dropDuplicates("product_Id")


    val datas = result_data.toJSON.rdd.map(line => {
      val json = JSON.parseObject(line)
      val regid_info = json.getOrDefault("region_id_info", "-1").toString
      val region_id = json.getOrDefault("region_id", "-1").toString
      val district_out = json.getOrDefault("district_out", "0").toString
      json.put("district_out", district_out)
      val economic_division_out = json.getOrDefault("economic_division_out", "-1").toString
      val province_out = json.getOrDefault("province_out", "-1").toString

      var region_id_info = "0"
      var region_id_out = "0"

      if (regid_info.length == 2) {
        region_id_info = regid_info + "0000"
        json.put("region_id_info", region_id_info)
      } else if (regid_info.length == 4) {
        region_id_info = regid_info + "00"
        json.put("region_id_info", region_id_info)
      } else if (regid_info.length == 6) {
        region_id_info = regid_info
        json.put("region_id_info", region_id_info)
      } else {
        region_id_info = "0"
        json.put("region_id_info", region_id_info)
      }


      if (region_id.length == 2) {
        region_id_out = region_id + "0000"
        json.put("region_id", region_id_out)
      } else if (region_id.length == 4) {
        region_id_out = region_id + "00"
        json.put("region_id", region_id_out)
      } else if (region_id.length == 6) {
        region_id_out = region_id
        json.put("region_id", region_id_out)
      } else {
        region_id_out = "0"
        json.put("region_id", region_id_out)
      }


      if (economic_division_out.equals("-1")) {
        json.put("economic_division_out", "0")
      } else if (economic_division_out.equals("西部地区")) {
        json.put("economic_division_out", "4")
      } else if (economic_division_out.equals("东部地区")) {
        json.put("economic_division_out", "2")
      } else if (economic_division_out.equals("中部地区")) {
        json.put("economic_division_out", "3")
      } else if (economic_division_out.equals("东北地区")) {
        json.put("economic_division_out", "1")
      } else if (economic_division_out.equals("港澳") || economic_division_out.equals("台湾")) {
        if (province_out.contains("香港")) {
          json.put("economic_division_out", "香港特别行政区")
        } else if (province_out.contains("澳门")) {
          json.put("economic_division_out", "澳门特别行政区")
        } else if (province_out.contains("台湾")) {
          json.put("economic_division_out", "台湾省")
        }
      }

      json.toJSONString

    })


    val address_res = sqlContext.read.json(datas)
    address_res.registerTempTable("result")

    //旅游和游轮
    //        val result = sqlContext.sql(
    //          """
    //            |select
    //            |*,
    //            |case when Country_out !='中国'  then '0' else province_out end as province_out_v1,
    //            |case when Country_out !='中国' then '0' else city_out end as city_out_v1,
    //            |case when Country_out !='中国' then '0' else district_out end as district_out_v1
    //            |from
    //            |address_res
    //          """.stripMargin).drop("province_out", "city_out", "district_out")
    //          .withColumnRenamed("province_out_v1", "province_out")
    //          .withColumnRenamed("city_out_v1", "city_out")
    //          .withColumnRenamed("district_out_v1", "district_out")
    //        result.registerTempTable("result")

    //修改门票国家
    val result_v2 = sqlContext.sql(
      """
        |select
        |*,
        |case
        |when province_out in ("湖南省"
        |,"甘肃省"
        |,"新疆维吾尔自治区"
        |,"安徽省"
        |,"贵州省"
        |,"河南省"
        |,"云南省"
        |,"北京市"
        |,"辽宁省"
        |,"浙江省"
        |,"西藏自治区"
        |,"上海市"
        |,"重庆市"
        |,"青海省"
        |,"海南省"
        |,"澳门特别行政区"
        |,"宁夏回族自治区"
        |,"山西省"
        |,"湖北省"
        |,"江西省"
        |,"四川省"
        |,"广东省"
        |,"山东省"
        |,"内蒙古自治区"
        |,"江苏省"
        |,"福建省"
        |,"天津市"
        |,"广西壮族自治区"
        |,"陕西省"
        |,"黑龙江省"
        |,"吉林省"
        |,"河北省","香港特别行政区","澳门特别行政区","台湾省")  then '中国' else '0' end as Country_out_v1,
        |case when province_out='0' and city_out='0' and district_out='0' then '0' else region_id end as region_id_v1,
        |case when province_out in ('北京市','上海市','天津市','重庆市') then province_out else city_out end as city_out_v1,
        |case when province_out in ("湖南省"
        |,"甘肃省"
        |,"新疆维吾尔自治区"
        |,"安徽省"
        |,"贵州省"
        |,"河南省"
        |,"云南省"
        |,"北京市"
        |,"辽宁省"
        |,"浙江省"
        |,"西藏自治区"
        |,"上海市"
        |,"重庆市"
        |,"青海省"
        |,"海南省"
        |,"宁夏回族自治区"
        |,"山西省"
        |,"湖北省"
        |,"江西省"
        |,"四川省"
        |,"广东省"
        |,"山东省"
        |,"内蒙古自治区"
        |,"江苏省"
        |,"福建省"
        |,"天津市"
        |,"广西壮族自治区"
        |,"陕西省"
        |,"黑龙江省"
        |,"吉林省"
        |,"河北省") then '03001' else '03002' end as toursim_tickets_type_v1
        |from
        |result
              """.stripMargin).drop("Country_out", "region_id", "city_out", "toursim_tickets_type")
      .withColumnRenamed("Country_out_v1", "Country_out")
      .withColumnRenamed("region_id_v1", "region_id")
      .withColumnRenamed("city_out_v1", "city_out")
      .withColumnRenamed("toursim_tickets_type_v1", "toursim_tickets_type")
    //mp:String,zhujian
//    toes_ly(result_v2, year, month, s"${plat_type}", "product_Id")
    //          }
    //        }
    result_v2

  }

  /**
    * 门票
    *
    * @param sc
    * @param sqlContext
    * @param year
    * @param month
    * @param estype
    */
  def mp(frame:DataFrame,sc: SparkContext, sqlContext: SQLContext, year: String, month: String, estype: String): DataFrame = {
    //    for(estype <-Array("mp")){

    //"1","2","3","4","5","6","7","8","9","10","11"
    //      for (year <- Array("2020")) {
    //        for (month <- Array("4")) {
//    println("mp::::::::month:" + s"${month}")
//    val es_data: RDD[String] = sc.esJsonRDD(s"${year}_update_${platform}_${estype}/${platform}_${estype}_${year}_${month}").values

    val frame1 = frame
      .drop("region_id_info", "region_id", "economic_division_out", "economic_division_info", "province_out", "city_out")
    print("++++++++++++++")
    frame1.show(10)
    val datas1 = frame1.toJSON.rdd.map(line => {
      val nObject = JSON.parseObject(line)
      val province = nObject.getOrDefault("province", "0")
      val city = nObject.getOrDefault("city", "0")
      val economic_division = nObject.getOrDefault("economic_division", "0")
      val regional_ID = nObject.getOrDefault("regional_ID", "0").toString
      val category_tag = nObject.getOrDefault("category_tag", "-1")
      //math_cate category_tag
      nObject.put("province_out", province)
      nObject.put("city_out", city)
      nObject.put("economic_division_out", economic_division)
      nObject.put("region_id", regional_ID)
      nObject.put("category_tag", category_tag)
      nObject.toJSONString
    })

    //修改目的地region_id目的地：国家 Country_out、省 province_out、市 city_out、县 district_out、区域id region_id(能到几位打几位)
    sqlContext.read.json(datas1)
      .registerTempTable("mudidi_data")
    //补充默认值0
    val result_data = sqlContext.sql(
      """
        |select
        |*,
        |--case when Country_out='0'  or Country_out is null or Country_out='' or Country_out='-1' then '0' else Country_out end as Country_out_v1,
        |case when province_out='0' or province_out='海外'  or province_out is null or province_out='' or province_out='-1' then '0' else province_out end as province_out_v1,
        |case when city_out='0' or city_out='海外' or city_out is null or city_out='' or city_out='-1' then '0' else city_out end as city_out_v1,
        |case when district_out='0' or district_out='海外' or district_out is null or district_out='' or district_out='-1' then '0' else district_out end as district_out_v1,
        |case when regional_ID='999999' or regional_ID='-1' or regional_ID='99'  then '0' else regional_ID end as region_id,
        |economic_division as economic_division_out
        |
        |from
        |mudidi_data
            """.stripMargin).dropDuplicates("good_id").drop("province_out", "city_out", "district_out")
      .withColumnRenamed("province_out_v1", "province_out")
      .withColumnRenamed("city_out_v1", "city_out")
      .withColumnRenamed("district_out_v1", "district_out")
    val s = math_cate(sqlContext.sparkSession, result_data)
    //补全区域id为6位，修改出发地四大区为名称
    val datas = s.toJSON.rdd.map(line => {
      val json = JSON.parseObject(line)
      val regid_info = json.getOrDefault("region_id_info", "0").toString
      val region_id = json.getOrDefault("region_id", "0").toString
      val district_out = json.getOrDefault("district_out", "0").toString
      json.put("district_out", district_out)

      val economic_division_out = json.getOrDefault("economic_division_out", "0").toString
      val province_out = json.getOrDefault("province_out", "0").toString

      var region_id_info = "0"
      var region_id_out = "0"

      if (regid_info.length == 2) {
        region_id_info = regid_info + "0000"
        json.put("region_id_info", region_id_info)
      } else if (regid_info.length == 4) {
        region_id_info = regid_info + "00"
        json.put("region_id_info", region_id_info)
      } else if (regid_info.length == 6) {
        region_id_info = regid_info
        json.put("region_id_info", region_id_info)
      } else {
        region_id_info = "0"
        json.put("region_id_info", region_id_info)
      }


      if (region_id.length == 2) {
        region_id_out = region_id + "0000"
        json.put("region_id", region_id_out)
      } else if (region_id.length == 4) {
        region_id_out = region_id + "00"
        json.put("region_id", region_id_out)
      } else if (region_id.length == 6) {
        region_id_out = region_id
        json.put("region_id", region_id_out)
      } else {
        region_id_out = "0"
        json.put("region_id", region_id_out)
      }


      if (economic_division_out.equals("-1")) {
        json.put("economic_division_out", "0")
      } else if (economic_division_out.equals("西部地区")) {
        json.put("economic_division_out", "4")
      } else if (economic_division_out.equals("东部地区")) {
        json.put("economic_division_out", "2")
      } else if (economic_division_out.equals("中部地区")) {
        json.put("economic_division_out", "3")
      } else if (economic_division_out.equals("东北地区")) {
        json.put("economic_division_out", "1")
      } else if (economic_division_out.equals("港澳") || economic_division_out.equals("台湾")) {
        if (province_out.contains("香港")) {
          json.put("economic_division_out", "香港特别行政区")
        } else if (province_out.contains("澳门")) {
          json.put("economic_division_out", "澳门特别行政区")
        } else if (province_out.contains("台湾")) {
          json.put("economic_division_out", "台湾省")
        }
      }

      json.toJSONString

    })

    //修改门票国家
    sqlContext.read.json(datas).registerTempTable("address_t1")
    val result_v2 = sqlContext.sql(
      """
        |select
        |*,
        |case
        |when province_out in ("湖南省"
        |,"甘肃省"
        |,"新疆维吾尔自治区"
        |,"安徽省"
        |,"贵州省"
        |,"河南省"
        |,"云南省"
        |,"北京市"
        |,"辽宁省"
        |,"浙江省"
        |,"西藏自治区"
        |,"上海市"
        |,"重庆市"
        |,"青海省"
        |,"海南省"
        |,"澳门特别行政区"
        |,"宁夏回族自治区"
        |,"山西省"
        |,"湖北省"
        |,"江西省"
        |,"四川省"
        |,"广东省"
        |,"山东省"
        |,"内蒙古自治区"
        |,"江苏省"
        |,"福建省"
        |,"天津市"
        |,"广西壮族自治区"
        |,"陕西省"
        |,"黑龙江省"
        |,"吉林省"
        |,"河北省","香港特别行政区","澳门特别行政区","台湾省") then '中国' else '0' end as Country_out_v1,
        |case when province_out='0' and city_out='0' and district_out='0' then '0' else region_id end as region_id_v1,
        |case when province_out in ('北京市','上海市','天津市','重庆市') then province_out else city_out end as city_out_v1,
        |case when province_out in ("湖南省"
        |,"甘肃省"
        |,"新疆维吾尔自治区"
        |,"安徽省"
        |,"贵州省"
        |,"河南省"
        |,"云南省"
        |,"北京市"
        |,"辽宁省"
        |,"浙江省"
        |,"西藏自治区"
        |,"上海市"
        |,"重庆市"
        |,"青海省"
        |,"海南省"
        |,"宁夏回族自治区"
        |,"山西省"
        |,"湖北省"
        |,"江西省"
        |,"四川省"
        |,"广东省"
        |,"山东省"
        |,"内蒙古自治区"
        |,"江苏省"
        |,"福建省"
        |,"天津市"
        |,"广西壮族自治区"
        |,"陕西省"
        |,"黑龙江省"
        |,"吉林省"
        |,"河北省") then '03001' else '03002' end as toursim_tickets_type_v1
        |from
        |address_t1
            """.stripMargin).drop("Country_out", "region_id", "city_out", "toursim_tickets_type")
      .withColumnRenamed("Country_out_v1", "Country_out")
      .withColumnRenamed("region_id_v1", "region_id")
      .withColumnRenamed("city_out_v1", "city_out")
      .withColumnRenamed("toursim_tickets_type_v1", "toursim_tickets_type")

    //匹配不上地址的去city库匹配
    //                      addressclear(sc,sqlContext,result_v2)

    val frame2 = addressclear(sc, sqlContext, result_v2, estype)
    frame2
    //    result_v2
    //        }
    //      }
    //    }
  }

  /**
    * 地址特殊处理
    *
    * @param sc
    * @param sqlContext
    * @param data
    * @return
    */
  def addressclear(sc: SparkContext, sqlContext: SQLContext, data: DataFrame, estype: String): DataFrame = {
    data.show()
    data.registerTempTable("data")
    //需要地址处理的
    val data1 = sqlContext.sql(
      """
        |select
        |*
        |from
        |data
        |where first_arriving !='-1' --and province_out='0'
      """.stripMargin)
      .drop("Country_out", "region_id", "city_out", "province_out", "toursim_tickets_type", "district_out")
    data1.registerTempTable("data1")
    //


    /** ***************************特殊操作需要注释 ***************************/

    var data3 = sqlContext.emptyDataFrame
    //不需要地址处理的
    if ("mp".equals(estype)) {
      data3 = sqlContext.sql(
        """
          |select
          |t1.*,
          |case when Country_out='中国' then (case when province_out in ('台湾省','香港特别行政区','澳门特别行政区','南海诸岛') then '03002' else '03001' end) else '03002' end as  toursim_tickets_type_1
          |from
          |data t1
          |left join
          |data1 t2
          |on t1.good_id=t2.good_id where t2.good_id is null
        """.stripMargin).dropDuplicates("good_id").drop("toursim_tickets_type")
        .withColumnRenamed("toursim_tickets_type_1", "toursim_tickets_type")
    } else {
      data3 = sqlContext.sql(
        """
          |select
          |t1.*,
          |case when Country_out='中国' then (case when province_out in ('台湾省','香港特别行政区','澳门特别行政区','南海诸岛') then '03002' else '03001' end) else '03002' end as  toursim_tickets_type_1
          |from
          |data t1
          |left join
          |data1 t2
          |on t1.product_Id=t2.product_Id where t2.product_Id is null
        """.stripMargin).dropDuplicates("product_Id").drop("toursim_tickets_type")
        .withColumnRenamed("toursim_tickets_type_1", "toursim_tickets_type")
    }
    /** ***************************特殊操作需要注释 ***************************/

    //和city库进行匹配
    val brandapi_vf = ReadConfig(Map(
      "spark.mongodb.input.uri" -> s"mongodb://root:O2Odata123!@ 192.168.0.149:27017/admin"
      , "spark.mongodb.input.database" -> s"Quna"
      , "spark.mongodb.input.collection" -> s"city"))
    val frame = sqlContext.read.json(MongoSpark.load(sc, brandapi_vf).map(line => {
      val nObject = JSON.parseObject(line.toJson())
      nObject.remove("_id")
      nObject.toJSONString
    }))
    frame.registerTempTable("frame")
    //    frame.show()
    var data2 = sqlContext.emptyDataFrame
    if ("mp".equals(estype)) {
      data2 = sqlContext.sql(
        """
          |select
          |*,
          |case when Country_out='中国' then (case when province_out in ('台湾省','香港特别行政区','澳门特别行政区','南海诸岛') then '03002' else '03001' end) else '03002' end as  toursim_tickets_type,
          |case
          |when title rlike '横店影视城年卡' then '东阳市'
          |when title rlike '合肥融创乐园' then '包河区'
          |else '0' end as district_out
          |from
          |(select
          |t1.*,
          |t2.Country as Country_out,
          |t2.regional_ID as region_id,
          |t2.City as city_out,
          |t2.Province as province_out
          |from
          |data1 t1
          |left join
          |frame t2
          |on t2.data rlike t1.first_arriving)
      """.stripMargin).dropDuplicates("good_id")
    } else {
      data2 = sqlContext.sql(
        """
          |select
          |*,
          |case when Country_out='中国' then (case when province_out in ('台湾省','香港特别行政区','澳门特别行政区','南海诸岛') then '03002' else '03001' end) else '03002' end as  toursim_tickets_type,
          |case
          |when product_title rlike '横店影视城年卡' then '东阳市'
          |when product_title rlike '合肥融创乐园' then '包河区'
          |else '0' end as district_out
          |from
          |(select
          |t1.*,
          |t2.Country as Country_out,
          |t2.regional_ID as region_id,
          |t2.City as city_out,
          |t2.Province as province_out
          |from
          |data1 t1
          |left join
          |frame t2
          |on t2.data rlike t1.first_arriving)
        """.stripMargin).dropDuplicates("product_Id")
    }
    data2.show()
    /**
      * 错误处理用第587行，正常跑用第588行
      */
    //    val value = data2.toJSON.rdd //587
    val value = data2.toJSON.rdd.union(data3.toJSON.rdd) // 588
    //    data3.show()
    sqlContext.read.json(value)
  }


  def toes_ly(data: DataFrame, year: String, month: String, mp: String, zhujian: String): Unit = {
    val value = data.toJSON.rdd.map(line => {
      val lines = JSON.parseObject(line)
      lines
    })
    value.saveToEs(s"${year}_update_${platform}_${mp}/${platform}_${mp}_${year}_${month}", Map("es.mapping.id" -> s"${zhujian}"))
  }

  //途牛门票调用这个方法之前  先给 category_tag 一个默认值'-1'   （"'-1' as category_tag"） math_cate category_tag
  def math_cate(spark: SparkSession, dataFrame: DataFrame): DataFrame = {

    dataFrame.createOrReplaceTempView("data_v")
    val data_cate = spark.sql(
      """
        |select
        |--good_id,
        |--title,
        |--address,
        |*,
        |case
        |when category_tag rlike "人文景观"  then "人文景观"
        |when category_tag rlike "动植物园"  then "动植物园"
        |when category_tag rlike "主题乐园"  then "主题乐园"
        |when category_tag rlike "名胜古迹"  then "名胜古迹"
        |when category_tag rlike "展馆展览"  then "展馆展览"
        |when category_tag rlike "冰雪世界"  then "冰雪世界"
        |when category_tag rlike "亲水避暑"  then "亲水避暑"
        |when category_tag rlike "演出演艺"  then "演出演艺"
        |when category_tag rlike "演艺演出"  then "演出演艺"
        |when category_tag rlike "自然风光"  then "自然风光"
        |
        |when category_tag rlike   "自然风光"              then    "自然风光"
        |when category_tag rlike   "公园"              then    "人文景观"
        |when category_tag rlike   "水世界"             then    "亲水避暑"
        |when category_tag rlike   "人文"              then    "人文景观"
        |when category_tag rlike   "休闲娱乐"              then    "演出演艺"
        |when category_tag rlike   "游乐场"             then    "主题乐园"
        |when category_tag rlike   "名人故居"              then    "名胜古迹"
        |when category_tag rlike   "河流湖泊"              then    "自然风光"
        |when category_tag rlike   "纪念馆"             then    "展馆展览"
        |when category_tag rlike   "赏花"              then    "动植物园"
        |when category_tag rlike   "山岳"              then    "自然风光"
        |when category_tag rlike   "植物园"             then    "动植物园"
        |when category_tag rlike   "溶洞"              then    "自然风光"
        |when category_tag rlike   "瀑布"              then    "自然风光"
        |when category_tag rlike   "展览"              then    "展馆展览"
        |when category_tag rlike   "博物馆"             then    "展馆展览"
        |when category_tag rlike   "海洋馆"             then    "展馆展览"
        |when category_tag rlike   "草原"              then    "自然风光"
        |when category_tag rlike   "森林"              then    "自然风光"
        |when category_tag rlike   "游船"              then    "亲水避暑"
        |when category_tag rlike   "历史遗迹"              then    "名胜古迹"
        |when category_tag rlike   "漂流"              then    "亲水避暑"
        |when category_tag rlike   "滑雪"              then    "冰雪世界"
        |when category_tag rlike   "动物园"             then    "动植物园"
        |when category_tag rlike   "峡谷"              then    "自然风光"
        |when category_tag rlike   "4D/5D电影"             then    "演出演艺"
        |when category_tag rlike   "宗教寺庙"              then    "名胜古迹"
        |when category_tag rlike   "园林"              then    "人文景观"
        |when category_tag rlike   "攀岩"              then    "人文景观"
        |when category_tag rlike   "城市观光"              then    "人文景观"
        |when category_tag rlike   "中心场馆"              then    "展馆展览"
        |when category_tag rlike   "蔬果采摘"              then    "动植物园"
        |when category_tag rlike   "演出赛事展览"              then    "展馆展览"
        |when category_tag rlike   "古镇"              then    "名胜古迹"
        |when category_tag rlike   "影视基地"              then    "人文景观"
        |when category_tag rlike   "生态农庄"              then    "动植物园"
        |when category_tag rlike   "3D体验馆"             then    "演出演艺"
        |when category_tag rlike   "温泉/汗蒸/SPA"             then    "亲水避暑"
        |when category_tag rlike   "岛屿"              then    "自然风光"
        |when category_tag rlike   "海滨"              then    "自然风光"
        |when category_tag rlike   "亲子玩乐"              then    "主题乐园"
        |when category_tag rlike   "儿童乐园"              then    "主题乐园"
        |when category_tag rlike   "演出"              then    "演出演艺"
        |when category_tag rlike   "湿地"              then    "自然风光"
        |when category_tag rlike   "泉潭"              then    "自然风光"
        |when category_tag rlike   "古村落"             then    "名胜古迹"
        |when category_tag rlike   "其他展览"              then    "展馆展览"
        |when category_tag rlike   "观光车"             then    "人文景观"
        |when category_tag rlike   "画展"              then    "人文景观"
        |when category_tag rlike   "科技馆"             then    "展馆展览"
        |when category_tag rlike   "沙漠"              then    "自然风光"
        |when category_tag rlike   "鬼屋"              then    "主题乐园"
        |when category_tag rlike   "蜡像馆"             then    "展馆展览"
        |when category_tag rlike   "陶艺"              then    "展馆展览"
        |when category_tag rlike   "户外游玩"              then    "自然风光"
        |when category_tag rlike   "动漫"              then    "人文景观"
        |when category_tag rlike   "演出游玩"              then    "演出演艺"
        |when category_tag rlike   "话剧舞台剧"             then    "演出演艺"
        |when category_tag rlike   "主题乐园"              then    "主题乐园"
        |when category_tag rlike   "动植物园"              then    "动植物园"
        |when category_tag rlike   "人文历史"              then    "人文景观"
        |--myself
        |when category_tag rlike   "峨眉山"              then    "自然风光"
        |when category_tag rlike   "青城山"              then    "自然风光"
        |when category_tag rlike   "民俗园"              then    "人文景观"
        |when category_tag rlike   "马蹄寺"              then    "名胜古迹"
        |when category_tag rlike   "温泉"              then    "亲水避暑"
        |when category_tag rlike   "乐园"              then    "主题乐园"
        |when category_tag rlike   "千佛山"              then    "人文景观"
        |when category_tag rlike   "水族"              then    "亲水避暑"
        |when category_tag rlike   "海洋"              then    "亲水避暑"
        |when category_tag rlike   "庙"              then    "名胜古迹"
        |when category_tag rlike   "船"              then    "亲水避暑"
        |when category_tag rlike   "海豚"              then    "主题乐园"
        |when category_tag rlike   "海豚"              then    "主题乐园"
        |when category_tag rlike   "亲子"              then    "主题乐园"
        |when category_tag rlike   "敦煌"              then    "自然风光"
        |when category_tag rlike   "大佛"              then    "名胜古迹"
        |when category_tag rlike   "方特"              then    "主题乐园"
        |when category_tag rlike   "水镇"              then    "亲水避暑"
        |when category_tag rlike   "古城"              then    "人文景观"
        |when category_tag rlike   "长城"              then    "名胜古迹"
        |when category_tag rlike   "迷宫"              then    "主题乐园"
        |when category_tag rlike   "登高"              then    "人文景观"
        |when category_tag rlike   "斗兽场"             then    "名胜古迹"
        |when category_tag rlike   "歌剧院"             then    "演出演艺"
        |when category_tag rlike   "杜甫草堂"           then    "名胜古迹"
        |when category_tag rlike   "欢乐谷"            then     "主题乐园"
        |when category_tag rlike   "水上"            then     "亲水避暑"
        |
        |
        |--2
        |when category_tag rlike "人文景观" then "人文景观"
        |when category_tag rlike "动物" then "动植物园"
        |when category_tag rlike "植物" then "动植物园"
        |when category_tag rlike "主题乐园" then "主题乐园"
        |when category_tag rlike "名胜" then "名胜古迹"
        |when category_tag rlike "古迹" then "名胜古迹"
        |when category_tag rlike "展馆" then "展馆展览"
        |when category_tag rlike "展览" then "展馆展览"
        |when category_tag rlike "冰雪" then "冰雪世界"
        |when category_tag rlike "避暑" then "亲水避暑"
        |when category_tag rlike "亲水" then "亲水避暑"
        |when category_tag rlike "演出" then "演出演艺"
        |when category_tag rlike "演艺" then "演出演艺"
        |
        |
        |
        |
        |--1
        |when title rlike   "自然风光"              then    "自然风光"
        |when title rlike   "公园"              then    "人文景观"
        |when title rlike   "水世界"             then    "亲水避暑"
        |when title rlike   "人文"              then    "人文景观"
        |when title rlike   "休闲娱乐"              then    "演出演艺"
        |when title rlike   "游乐场"             then    "主题乐园"
        |when title rlike   "名人故居"              then    "名胜古迹"
        |when title rlike   "河流湖泊"              then    "自然风光"
        |when title rlike   "纪念馆"             then    "展馆展览"
        |when title rlike   "赏花"              then    "动植物园"
        |when title rlike   "山岳"              then    "自然风光"
        |when title rlike   "植物园"             then    "动植物园"
        |when title rlike   "溶洞"              then    "自然风光"
        |when title rlike   "瀑布"              then    "自然风光"
        |when title rlike   "展览"              then    "展馆展览"
        |when title rlike   "博物馆"             then    "展馆展览"
        |when title rlike   "海洋馆"             then    "展馆展览"
        |when title rlike   "草原"              then    "自然风光"
        |when title rlike   "森林"              then    "自然风光"
        |when title rlike   "游船"              then    "亲水避暑"
        |when title rlike   "历史遗迹"              then    "名胜古迹"
        |when title rlike   "漂流"              then    "亲水避暑"
        |when title rlike   "滑雪"              then    "冰雪世界"
        |when title rlike   "动物园"             then    "动植物园"
        |when title rlike   "峡谷"              then    "自然风光"
        |when title rlike   "4D/5D电影"             then    "演出演艺"
        |when title rlike   "宗教寺庙"              then    "名胜古迹"
        |when title rlike   "园林"              then    "人文景观"
        |when title rlike   "攀岩"              then    "人文景观"
        |when title rlike   "城市观光"              then    "人文景观"
        |when title rlike   "中心场馆"              then    "展馆展览"
        |when title rlike   "蔬果采摘"              then    "动植物园"
        |when title rlike   "演出赛事展览"              then    "展馆展览"
        |when title rlike   "古镇"              then    "名胜古迹"
        |when title rlike   "影视基地"              then    "人文景观"
        |when title rlike   "生态农庄"              then    "动植物园"
        |when title rlike   "3D体验馆"             then    "演出演艺"
        |when title rlike   "温泉/汗蒸/SPA"             then    "亲水避暑"
        |when title rlike   "岛屿"              then    "自然风光"
        |when title rlike   "海滨"              then    "自然风光"
        |when title rlike   "亲子玩乐"              then    "主题乐园"
        |when title rlike   "儿童乐园"              then    "主题乐园"
        |when title rlike   "演出"              then    "演出演艺"
        |when title rlike   "湿地"              then    "自然风光"
        |when title rlike   "泉潭"              then    "自然风光"
        |when title rlike   "古村落"             then    "名胜古迹"
        |when title rlike   "其他展览"              then    "展馆展览"
        |when title rlike   "观光车"             then    "人文景观"
        |when title rlike   "画展"              then    "人文景观"
        |when title rlike   "科技馆"             then    "展馆展览"
        |when title rlike   "沙漠"              then    "自然风光"
        |when title rlike   "鬼屋"              then    "主题乐园"
        |when title rlike   "蜡像馆"             then    "展馆展览"
        |when title rlike   "陶艺"              then    "展馆展览"
        |when title rlike   "户外游玩"              then    "自然风光"
        |when title rlike   "动漫"              then    "人文景观"
        |when title rlike   "演出游玩"              then    "演出演艺"
        |when title rlike   "话剧舞台剧"             then    "演出演艺"
        |when title rlike   "主题乐园"              then    "主题乐园"
        |when title rlike   "动植物园"              then    "动植物园"
        |when title rlike   "人文历史"              then    "人文景观"
        |--myself
        |when title rlike   "鼓鸣岩"              then    "自然风光"
        |when title rlike   "古风洞"              then    "自然风光"
        |when title rlike   "峨眉山"              then    "自然风光"
        |when title rlike   "青城山"              then    "自然风光"
        |when title rlike   "民俗园"              then    "人文景观"
        |when title rlike   "马蹄寺"              then    "名胜古迹"
        |when title rlike   "温泉"              then    "亲水避暑"
        |when title rlike   "乐园"              then    "主题乐园"
        |when title rlike   "千佛山"              then    "人文景观"
        |when title rlike   "水族"              then    "亲水避暑"
        |when title rlike   "海洋"              then    "亲水避暑"
        |when title rlike   "庙"              then    "名胜古迹"
        |when title rlike   "船"              then    "亲水避暑"
        |when title rlike   "海豚"              then    "主题乐园"
        |when title rlike   "海豚"              then    "主题乐园"
        |when title rlike   "亲子"              then    "主题乐园"
        |when title rlike   "敦煌"              then    "自然风光"
        |when title rlike   "大佛"              then    "名胜古迹"
        |when title rlike   "方特"              then    "主题乐园"
        |when title rlike   "摩天轮"              then    "主题乐园"
        |when title rlike   "水镇"              then    "亲水避暑"
        |when title rlike   "古城"              then    "人文景观"
        |when title rlike   "长城"              then    "名胜古迹"
        |when title rlike   "迷宫"              then    "主题乐园"
        |when title rlike   "登高"              then    "人文景观"
        |when title rlike   "斗兽场"             then    "名胜古迹"
        |when title rlike   "歌剧院"             then    "演出演艺"
        |when title rlike   "杜甫草堂"           then    "名胜古迹"
        |when title rlike   "欢乐谷"            then     "主题乐园"
        |when title rlike   "水上"            then     "亲水避暑"
        |
        |
        |--2
        |when title rlike "人文景观" then "人文景观"
        |when title rlike "动物" then "动植物园"
        |when title rlike "植物" then "动植物园"
        |when title rlike "主题乐园" then "主题乐园"
        |when title rlike "名胜" then "名胜古迹"
        |when title rlike "古迹" then "名胜古迹"
        |when title rlike "展馆" then "展馆展览"
        |when title rlike "展览" then "展馆展览"
        |when title rlike "冰雪" then "冰雪世界"
        |when title rlike "避暑" then "亲水避暑"
        |when title rlike "亲水" then "亲水避暑"
        |when title rlike "演出" then "演出演艺"
        |when title rlike "演艺" then "演出演艺"
        |
        |else '其他'  end category_tags
        |
        |from data_v
      """.stripMargin)
      .drop("category_tag")
      .withColumnRenamed("category_tags", "category_tag")
      .dropDuplicates()


    data_cate.createOrReplaceTempView("data_v1")
    val data_cate1 = spark.sql(
      """
        |select
        |--good_id,
        |--title,
        |--address,
        |*,
        |case
        |--20201215
        |when category_tag ='其他' and title rlike "艺术馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "减压馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "解压馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "美术馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "体验馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "科学馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "体验展" then "展馆展览"
        |when category_tag ='其他' and title rlike "影院" then "展馆展览"
        |when category_tag ='其他' and title rlike "全球巡展" then "展馆展览"
        |when category_tag ='其他' and title rlike "陈列馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "星奇馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "赏石园" then "展馆展览"
        |when category_tag ='其他' and title rlike "艺术区" then "展馆展览"
        |when category_tag ='其他' and title rlike "特展" then "展馆展览"
        |when category_tag ='其他' and title rlike "海底世界" then "展馆展览"
        |when category_tag ='其他' and title rlike "文创节" then "展馆展览"
        |when category_tag ='其他' and title rlike "主题馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "艺术空间" then "展馆展览"
        |when category_tag ='其他' and title rlike "恋爱馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "艺术展" then "展馆展览"
        |when category_tag ='其他' and title rlike "海澜飞马水城" then "展馆展览"
        |when category_tag ='其他' and title rlike "光影展" then "展馆展览"
        |when category_tag ='其他' and title rlike "大典" then "展馆展览"
        |when category_tag ='其他' and title rlike "魔幻城" then "展馆展览"
        |when category_tag ='其他' and title rlike "科学技术馆" then "展馆展览"
        |when category_tag ='其他' and title rlike "科学中心" then "展馆展览"
        |when category_tag ='其他' and title rlike "恐龙园" then "展馆展览"
        |when category_tag ='其他' and title rlike "艺术中心" then "展馆展览"
        |when category_tag ='其他' and title rlike "博物院" then "展馆展览"
        |when category_tag ='其他' and title rlike "科普" then "展馆展览"
        |
        |
        |when category_tag ='其他' and title rlike "密室逃脱" then "主题乐园"
        |when category_tag ='其他' and title rlike "惊魂" then "主题乐园"
        |when category_tag ='其他' and title rlike "密境" then "主题乐园"
        |when category_tag ='其他' and title rlike "电影世界" then "主题乐园"
        |when category_tag ='其他' and title rlike "欢乐世界" then "主题乐园"
        |when category_tag ='其他' and title rlike "飞行公馆" then "主题乐园"
        |when category_tag ='其他' and title rlike "蹦床街区" then "主题乐园"
        |when category_tag ='其他' and title rlike "融创海世界" then "主题乐园"
        |when category_tag ='其他' and title rlike "融创雪世界" then "主题乐园"
        |when category_tag ='其他' and title rlike "滑草场" then "主题乐园"
        |when category_tag ='其他' and title rlike "影视城" then "主题乐园"
        |when category_tag ='其他' and title rlike "嘉年华" then "主题乐园"
        |when category_tag ='其他' and title rlike "魔法城" then "主题乐园"
        |
        |
        |when category_tag ='其他' and title rlike "华清宫" then "名胜古迹"
        |when category_tag ='其他' and title rlike "鹳雀楼" then "名胜古迹"
        |when category_tag ='其他' and title rlike "黄鹤楼" then "名胜古迹"
        |when category_tag ='其他' and title rlike "明孝陵" then "名胜古迹"
        |when category_tag ='其他' and title rlike "布达拉宫" then "名胜古迹"
        |when category_tag ='其他' and title rlike "雁门关" then "名胜古迹"
        |when category_tag ='其他' and title rlike "应县木塔" then "名胜古迹"
        |when category_tag ='其他' and title rlike "黄帝陵" then "名胜古迹"
        |when category_tag ='其他' and title rlike "天门寺" then "名胜古迹"
        |when category_tag ='其他' and title rlike "天目湖" then "名胜古迹"
        |when category_tag ='其他' and title rlike "西夏陵" then "名胜古迹"
        |when category_tag ='其他' and title rlike "雷峰塔" then "名胜古迹"
        |when category_tag ='其他' and title rlike "井冈山" then "名胜古迹"
        |when category_tag ='其他' and title rlike "泸定桥" then "名胜古迹"
        |when category_tag ='其他' and title rlike "家大院" then "名胜古迹"
        |when category_tag ='其他' and title rlike "府衙" then "名胜古迹"
        |when category_tag ='其他' and title rlike "故宫" then "名胜古迹"
        |when category_tag ='其他' and title rlike "花戏楼" then "名胜古迹"
        |when category_tag ='其他' and title rlike "墓" then "名胜古迹"
        |when category_tag ='其他' and title rlike "县衙" then "名胜古迹"
        |
        |
        |when category_tag ='其他' and title rlike "东方明珠" then "人文景观"
        |when category_tag ='其他' and title rlike "广州塔" then "人文景观"
        |when category_tag ='其他' and title rlike "观光厅" then "人文景观"
        |when category_tag ='其他' and title rlike "世外桃源" then "人文景观"
        |when category_tag ='其他' and title rlike "古村" then "人文景观"
        |when category_tag ='其他' and title rlike "古镇" then "人文景观"
        |when category_tag ='其他' and title rlike "文化园" then "人文景观"
        |when category_tag ='其他' and title rlike "清明上河园" then "人文景观"
        |when category_tag ='其他' and title rlike "祠" then "人文景观"
        |when category_tag ='其他' and title rlike "美龄宫" then "人文景观"
        |when category_tag ='其他' and title rlike "人民解放纪念碑" then "人文景观"
        |when category_tag ='其他' and title rlike "水立方" then "人文景观"
        |when category_tag ='其他' and title rlike "海上田园" then "人文景观"
        |when category_tag ='其他' and title rlike "书院" then "人文景观"
        |when category_tag ='其他' and title rlike "中央电视塔" then "人文景观"
        |when category_tag ='其他' and title rlike "三潭印月" then "人文景观"
        |when category_tag ='其他' and title rlike "芙蓉镇" then "人文景观"
        |when category_tag ='其他' and title rlike "度假区" then "人文景观"
        |when category_tag ='其他' and title rlike "大观园" then "人文景观"
        |when category_tag ='其他' and title rlike "祖居" then "人文景观"
        |when category_tag ='其他' and title rlike "故居" then "人文景观"
        |when category_tag ='其他' and title rlike "旧居" then "人文景观"
        |when category_tag ='其他' and title rlike "大观园" then "人文景观"
        |when category_tag ='其他' and title rlike "故里" then "人文景观"
        |when category_tag ='其他' and title rlike "颐和园" then "人文景观"
        |when category_tag ='其他' and title rlike "城墙" then "人文景观"
        |when category_tag ='其他' and title rlike "石窟" then "人文景观"
        |
        |
        |when category_tag ='其他' and title rlike "猴岛" then "动植物园"
        |when category_tag ='其他' and title rlike "大熊猫" then "动植物园"
        |
        |
        |when category_tag ='其他' and title rlike "二人转" then "演出演艺"
        |when category_tag ='其他' and title rlike "马戏" then "演出演艺"
        |when category_tag ='其他' and title rlike "剧场" then "演出演艺"
        |when category_tag ='其他' and title rlike "皮影戏" then "演出演艺"
        |
        |
        |when category_tag ='其他' and title rlike "赛艇" then "运动户外"
        |when category_tag ='其他' and title rlike "体育世界" then "运动户外"
        |when category_tag ='其他' and title rlike "玻璃吊桥" then "运动户外"
        |when category_tag ='其他' and title rlike "卡丁车" then "运动户外"
        |when category_tag ='其他' and title rlike "保龄球" then "运动户外"
        |when category_tag ='其他' and title rlike "网球" then "运动户外"
        |when category_tag ='其他' and title rlike "高尔夫" then "运动户外"
        |when category_tag ='其他' and title rlike "体育小镇" then "运动户外"
        |when category_tag ='其他' and title rlike "运动港" then "运动户外"
        |when category_tag ='其他' and title rlike "轻极限" then "运动户外"
        |when category_tag ='其他' and title rlike "极限运动" then "运动户外"
        |when category_tag ='其他' and title rlike "直升飞机" then "运动户外"
        |when category_tag ='其他' and title rlike "飞行" then "运动户外"
        |when category_tag ='其他' and title rlike "潜水" then "运动户外"
        |when category_tag ='其他' and title rlike "游泳" then "运动户外"
        |when category_tag ='其他' and title rlike "滑翔伞" then "运动户外"
        |when category_tag ='其他' and title rlike "热气球" then "运动户外"
        |when category_tag ='其他' and title rlike "滑冰场" then "运动户外"
        |when category_tag ='其他' and title rlike "马术" then "运动户外"
        |when category_tag ='其他' and title rlike "马场" then "运动户外"
        |when category_tag ='其他' and title rlike "玻璃桥" then "运动户外"
        |when category_tag ='其他' and title rlike "户外" then "运动户外"
        |when category_tag ='其他' and title rlike "营地" then "运动户外"
        |when category_tag ='其他' and title rlike "探险" then "运动户外"
        |
        |
        |when category_tag ='其他' and title rlike "山庄" then "民俗度假"
        |when category_tag ='其他' and title rlike "度假村" then "民俗度假"
        |when category_tag ='其他' and title rlike "牧场" then "民俗度假"
        |when category_tag ='其他' and title rlike "客家" then "民俗度假"
        |when category_tag ='其他' and title rlike "农庄" then "民俗度假"
        |when category_tag ='其他' and title rlike "田园" then "民俗度假"
        |when category_tag ='其他' and title rlike "农业园" then "民俗度假"
        |when category_tag ='其他' and title rlike "庄园" then "民俗度假"
        |when category_tag ='其他' and title rlike "农场" then "民俗度假"
        |when category_tag ='其他' and title rlike "果园" then "民俗度假"
        |when category_tag ='其他' and title rlike "一寨两国" then "民俗度假"
        |when category_tag ='其他' and title rlike "生态" then "民俗度假"
        |when category_tag ='其他' and title rlike "观光园" then "民俗度假"
        |when category_tag ='其他' and title rlike "民族村" then "民俗度假"
        |when category_tag ='其他' and title rlike "农垦" then "民俗度假"
        |when category_tag ='其他' and title rlike "度假世界" then "民俗度假"
        |when category_tag ='其他' and title rlike "酒庄" then "民俗度假"
        |when category_tag ='其他' and title rlike "体验园" then "民俗度假"
        |when category_tag ='其他' and title rlike "农业" then "民俗度假"
        |
        |
        |when category_tag ='其他' and title rlike "洗浴" then "亲水避暑"
        |when category_tag ='其他' and title rlike "水疗" then "亲水避暑"
        |when category_tag ='其他' and title rlike "汤泉" then "亲水避暑"
        |when category_tag ='其他' and title rlike "汗蒸" then "亲水避暑"
        |when category_tag ='其他' and title rlike "浴馆" then "亲水避暑"
        |when category_tag ='其他' and title rlike "水汇" then "亲水避暑"
        |
        |
        |when category_tag ='其他' and title rlike "索道" then "自然风光"
        |when category_tag ='其他' and title rlike "黄龙洞" then "自然风光"
        |--wh category_tag ='其他' anden title rlike "武当山" then "自然风光"
        |--wh category_tag ='其他' anden title rlike "太姥山" then "自然风光"
        |when category_tag ='其他' and title rlike "大裂谷" then "自然风光"
        |when category_tag ='其他' and title rlike "佛爷洞" then "自然风光"
        |when category_tag ='其他' and title rlike "花海" then "自然风光"
        |when category_tag ='其他' and title rlike "仙境" then "自然风光"
        |when category_tag ='其他' and title rlike "石林" then "自然风光"
        |when category_tag ='其他' and title rlike "禹王洞" then "自然风光"
        |when category_tag ='其他' and title rlike "仙女湖" then "自然风光"
        |when category_tag ='其他' and title rlike "大纵湖" then "自然风光"
        |when category_tag ='其他' and title rlike "自然保护区" then "自然风光"
        |
        |
        |
        |when category_tag ='其他' and title rlike "小镇" then "民俗度假"
        |when category_tag ='其他' and title rlike "小城" then "民俗度假"
        |when category_tag ='其他' and title rlike "梯田" then "民俗度假"
        |  when category_tag ='其他' and title rlike "村" then "民俗度假"
        |when category_tag ='其他' and title rlike "屿" then "民俗度假"
        |when category_tag ='其他' and title rlike "寺" then "人文景观"
        |when category_tag ='其他' and title rlike "寨" then "人文景观"
        |when category_tag ='其他' and title rlike "亭" then "人文景观"
        |when category_tag ='其他' and title rlike "府" then "人文景观"
        |when category_tag ='其他' and title rlike "塔" then "人文景观"
        |when category_tag ='其他' and title rlike "阁" then "名胜古迹"
        |when category_tag ='其他' and title rlike "山" then "自然风光"
        |when category_tag ='其他' and title rlike "峰" then "自然风光"
        |when category_tag ='其他' and title rlike "岛" then "自然风光"
        |when category_tag ='其他' and title rlike "洞" then "自然风光"
        |when category_tag ='其他' and title rlike "画廊" then "自然风光"
        |
        |
        |
        |
        |else category_tag  end category_tag1
        |
        |from data_v1
      """.stripMargin)
      .drop("category_tag")
      .withColumnRenamed("category_tag1", "category_tag")
      .dropDuplicates()
    data_cate1
  }


}
